<?php
use Migrations\AbstractMigration;

class POCOR4570 extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function up()
    {
        // Backup table
        $this->execute('CREATE TABLE `zz_4570_report_queries` LIKE `report_queries`');
        $this->execute('INSERT INTO `zz_4570_report_queries` SELECT * FROM `report_queries`');

        $this->execute('CREATE TABLE `zz_4570_security_functions` LIKE `security_functions`');
        $this->execute('INSERT INTO `zz_4570_security_functions` SELECT * FROM `security_functions`');

        // Increase the size of name column
        $this->execute('ALTER TABLE `report_queries` MODIFY COLUMN `name` varchar(250)');

        // CREATE summary tables and INSERT new rows into report_queries table
        $this->execute('CREATE TABLE IF NOT EXISTS `summary_programme_sector_genders`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `institution_sector_id` int(11) NOT NULL, `institution_sector_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_system_id` int(11) NOT NULL, `education_system_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_id` int(11) NOT NULL, `education_level_isced_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_level` int(11) NOT NULL, `education_level_id` int(11) NOT NULL, `education_level_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_cycle_id` int(11) NOT NULL, `education_cycle_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_id` int(11) NOT NULL, `education_programme_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `gender_id` int(11) NOT NULL, `gender_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `total_students` int(11) NOT NULL, `total_staff_teaching` int(11) NOT NULL, `total_staff_teaching_newly_recruited` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_programme_sector_genders_truncate", "TRUNCATE summary_programme_sector_genders;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_programme_sector_genders_insert", "INSERT INTO summary_programme_sector_genders (academic_period_id, academic_period_name, institution_sector_id, institution_sector_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, education_level_id, education_level_name, education_cycle_id, education_cycle_name, education_programme_id, education_programme_code, education_programme_name, gender_id, gender_name, total_students, total_staff_teaching, total_staff_teaching_newly_recruited) SELECT subq1.academic_period_id ,subq1.academic_period_name ,subq1.institution_sector_id ,subq1.institution_sector_name ,subq1.education_system_id ,subq1.education_system_name ,subq1.education_level_isced_id ,subq1.education_level_isced_name ,subq1.education_level_isced_level ,subq1.education_level_id ,subq1.education_level_name ,subq1.education_cycle_id ,subq1.education_cycle_name ,subq1.education_programme_id ,subq1.education_programme_code ,subq1.education_programme_name ,subq1.gender_id ,subq1.gender_name ,subq1.total_students ,IFNULL(subq2.total_staff_teaching, 0) total_staff_teaching ,IFNULL(subq2.total_staff_teaching_newly_recruited, 0) total_staff_teaching_newly_recruited FROM ( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,education_levels.id education_level_id ,education_levels.name education_level_name ,education_cycles.id education_cycle_id ,education_cycles.name education_cycle_name ,education_programmes.id education_programme_id ,education_programmes.code education_programme_code ,education_programmes.name education_programme_name ,genders.id gender_id ,genders.name gender_name ,COUNT(DISTINCT(institution_students.student_id)) total_students FROM institution_students INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN genders ON genders.id = security_users.gender_id INNER JOIN education_grades ON education_grades.id = institution_students.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id WHERE institutions.institution_status_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) GROUP BY academic_periods.id ,institution_sectors.id ,education_level_isced.id ,education_programmes.id ,genders.id) subq1 LEFT JOIN ( SELECT subq.academic_period_id ,subq.academic_period_name ,subq.institution_sector_id ,subq.institution_sector_name ,subq.education_system_id ,subq.education_system_name ,subq.education_level_isced_id ,subq.education_level_isced_name ,subq.education_level_isced_level ,subq.education_level_id ,subq.education_level_name ,subq.education_cycle_id ,subq.education_cycle_name ,subq.education_programme_id ,subq.education_programme_code ,subq.education_programme_name ,subq.gender_id ,subq.gender_name ,COUNT(DISTINCT(subq.staff_id)) total_staff_teaching ,SUM(CASE WHEN subq.staff_start_date BETWEEN DATE_SUB(subq.academic_period_start_date,INTERVAL 12 MONTH) AND subq.academic_period_end_date THEN 1 ELSE 0 END) total_staff_teaching_newly_recruited FROM ( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,education_levels.id education_level_id ,education_levels.name education_level_name ,education_cycles.id education_cycle_id ,education_cycles.name education_cycle_name ,education_programmes.id education_programme_id ,education_programmes.code education_programme_code ,education_programmes.name education_programme_name ,genders.id gender_id ,genders.name gender_name ,institution_subject_staff.staff_id staff_id ,staff_data.staff_start_date staff_start_date ,academic_periods.start_date academic_period_start_date ,academic_periods.end_date academic_period_end_date FROM institution_subject_staff INNER JOIN institutions ON institutions.id = institution_subject_staff.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN ( SELECT institution_staff.staff_id ,institution_staff.institution_id ,MIN(institution_staff.start_date) staff_start_date FROM institution_staff INNER JOIN institution_positions ON institution_positions.id = institution_staff.institution_position_id AND institution_positions.institution_id = institution_staff.institution_id INNER JOIN staff_position_titles ON staff_position_titles.id = institution_positions.staff_position_title_id WHERE staff_position_titles.type = 1 GROUP BY institution_staff.staff_id ,institution_staff.institution_id ) staff_data ON staff_data.staff_id = institution_subject_staff.staff_id AND staff_data.institution_id = institution_subject_staff.institution_id INNER JOIN institution_subjects ON institution_subjects.id = institution_subject_staff.institution_subject_id INNER JOIN security_users ON security_users.id = institution_subject_staff.staff_id INNER JOIN genders ON genders.id = security_users.gender_id INNER JOIN education_grades ON education_grades.id = institution_subjects.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = institution_subjects.academic_period_id GROUP BY academic_periods.id ,institution_sectors.id ,education_level_isced.id ,education_programmes.id ,genders.id ,security_users.id ) subq GROUP BY subq.academic_period_id ,subq.institution_sector_id ,subq.education_level_isced_id ,subq.education_programme_id ,subq.gender_id ) subq2 ON subq1.academic_period_id = subq2.academic_period_id AND subq1.institution_sector_id = subq2.institution_sector_id AND subq1.education_level_isced_id = subq2.education_level_isced_id AND subq1.education_programme_id = subq2.education_programme_id AND subq1.gender_id = subq2.gender_id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
    

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_grade_gender_ages`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_system_id` int(11) NOT NULL, `education_system_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_id` int(11) NOT NULL, `education_level_isced_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_level` int(11) NOT NULL, `education_level_id` int(11) NOT NULL, `education_level_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_cycle_id` int(11) NOT NULL, `education_cycle_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_id` int(11) NOT NULL, `education_programme_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_grade_id` int(11) NOT NULL, `education_grade_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_grade_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `student_gender_id` int(11) NOT NULL, `student_gender_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `student_age` int(11) NOT NULL, `total_students` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_grade_gender_ages_truncate", "TRUNCATE summary_grade_gender_ages;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_grade_gender_ages_insert", "INSERT INTO summary_grade_gender_ages (academic_period_id, academic_period_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, education_level_id, education_level_name, education_cycle_id, education_cycle_name, education_programme_id, education_programme_code, education_programme_name, education_grade_id, education_grade_code, education_grade_name, student_gender_id, student_gender_name, student_age, total_students) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,education_levels.id education_level_id ,education_levels.name education_level_name ,education_cycles.id education_cycle_id ,education_cycles.name education_cycle_name ,education_programmes.id education_programme_id ,education_programmes.code education_programme_code ,education_programmes.name education_programme_name ,education_grades.id education_grade_id ,education_grades.code education_grade_code ,education_grades.name education_grade_name ,genders.id student_gender_id ,genders.name student_gender_name ,FLOOR(DATEDIFF(security_users.date_of_birth,academic_periods.start_date) / 365.25 * -1) student_age ,COUNT(DISTINCT(institution_students.student_id)) student_count FROM institution_students INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN genders ON genders.id = security_users.gender_id INNER JOIN education_grades ON education_grades.id = institution_students.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id WHERE institutions.institution_status_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) GROUP BY academic_periods.id ,education_level_isced.id ,education_grades.id ,genders.id ,FLOOR(DATEDIFF(security_users.date_of_birth,academic_periods.start_date) / 365.25 * -1);", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_grade_status_genders`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_system_id` int(11) NOT NULL, `education_system_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_id` int(11) NOT NULL, `education_level_isced_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_level` int(11) NOT NULL, `education_level_id` int(11) NOT NULL, `education_level_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_cycle_id` int(11) NOT NULL, `education_cycle_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_id` int(11) NOT NULL, `education_programme_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_grade_id` int(11) NOT NULL, `education_grade_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_grade_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `student_gender_id` int(11) NOT NULL, `student_gender_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `student_status_id` int(11) NOT NULL, `student_status_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `total_students` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_grade_status_genders_truncate", "TRUNCATE summary_grade_status_genders;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_grade_status_genders_insert", "INSERT INTO summary_grade_status_genders (academic_period_id, academic_period_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, education_level_id, education_level_name, education_cycle_id, education_cycle_name, education_programme_id, education_programme_code, education_programme_name, education_grade_id, education_grade_code, education_grade_name, student_gender_id, student_gender_name, student_status_id, student_status_name, total_students) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,education_levels.id education_level_id ,education_levels.name education_level_name ,education_cycles.id education_cycle_id ,education_cycles.name education_cycle_name ,education_programmes.id education_programme_id ,education_programmes.code education_programme_code ,education_programmes.name education_programme_name ,education_grades.id education_grade_id ,education_grades.code education_grade_code ,education_grades.name education_grade_name ,genders.id student_gender_id ,genders.name student_gender_name ,student_statuses.id student_status_id ,student_statuses.name student_status_name ,COUNT(DISTINCT(institution_students.student_id)) student_count FROM institution_students INNER JOIN student_statuses ON student_statuses.id = institution_students.student_status_id INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN genders ON genders.id = security_users.gender_id INNER JOIN education_grades ON education_grades.id = institution_students.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id WHERE institutions.institution_status_id = 1 GROUP BY academic_periods.id ,education_level_isced.id ,education_grades.id ,genders.id ,student_statuses.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_programme_sector_qualification_genders`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `institution_sector_id` int(11) NOT NULL, `institution_sector_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_system_id` int(11) NOT NULL, `education_system_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_id` int(11) NOT NULL, `education_level_isced_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_level` int(11) NOT NULL, `education_level_id` int(11) NOT NULL, `education_level_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_cycle_id` int(11) NOT NULL, `education_cycle_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_id` int(11) NOT NULL, `education_programme_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `staff_gender_id` int(11) NOT NULL, `staff_gender_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `staff_qualification_title_id` int(11) NOT NULL, `staff_qualification_title_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `total_staff_teaching` int(11) NOT NULL, `total_staff_teaching_newly_recruited` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_programme_sector_qualification_genders_truncate", "TRUNCATE summary_programme_sector_qualification_genders;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_programme_sector_qualification_genders_insert", "INSERT INTO summary_programme_sector_qualification_genders (academic_period_id, academic_period_name, institution_sector_id, institution_sector_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, education_level_id, education_level_name, education_cycle_id, education_cycle_name, education_programme_id, education_programme_code, education_programme_name, staff_gender_id, staff_gender_name, staff_qualification_title_id, staff_qualification_title_name, total_staff_teaching, total_staff_teaching_newly_recruited) SELECT subq.academic_period_id ,subq.academic_period_name ,subq.institution_sector_id ,subq.institution_sector_name ,subq.education_system_id ,subq.education_system_name ,subq.education_level_isced_id ,subq.education_level_isced_name ,subq.education_level_isced_level ,subq.education_level_id ,subq.education_level_name ,subq.education_cycle_id ,subq.education_cycle_name ,subq.education_programme_id ,subq.education_programme_code ,subq.education_programme_name ,subq.staff_gender_id ,subq.staff_gender_name ,subq.titles_ID staff_qualification_title_id ,subq.Title_Name staff_qualification_title_name ,COUNT(DISTINCT(subq.staff_id)) staff_teaching_count_total ,SUM(CASE WHEN subq.staff_start_date BETWEEN DATE_SUB(subq.academic_period_start_date,INTERVAL 12 MONTH) AND subq.academic_period_end_date THEN 1 ELSE 0 END) staff_teaching_newly_recruited_count FROM ( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,education_levels.id education_level_id ,education_levels.name education_level_name ,education_cycles.id education_cycle_id ,education_cycles.name education_cycle_name ,education_programmes.id education_programme_id ,education_programmes.code education_programme_code ,education_programmes.name education_programme_name ,genders.id staff_gender_id ,genders.name staff_gender_name ,qualification_staff.titles_ID ,qualification_staff.Title_Name ,institution_subject_staff.staff_id staff_id ,staff_data.staff_start_date staff_start_date ,academic_periods.start_date academic_period_start_date ,academic_periods.end_date academic_period_end_date FROM institution_subject_staff INNER JOIN institutions ON institutions.id = institution_subject_staff.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN ( SELECT institution_staff.staff_id ,institution_staff.institution_id ,MIN(institution_staff.start_date) staff_start_date FROM institution_staff INNER JOIN institution_positions ON institution_positions.id = institution_staff.institution_position_id AND institution_positions.institution_id = institution_staff.institution_id INNER JOIN staff_position_titles ON staff_position_titles.id = institution_positions.staff_position_title_id WHERE staff_position_titles.type = 1 GROUP BY institution_staff.staff_id ,institution_staff.institution_id) staff_data ON staff_data.staff_id = institution_subject_staff.staff_id AND staff_data.institution_id = institution_subject_staff.institution_id INNER JOIN ( SELECT staff_qualifications.staff_id staff_id ,staff_qualifications.qualification_title_id ,qualification_titles.id titles_ID ,qualification_titles.name Title_Name ,qualification_level.highest_qualification highest_qualification FROM staff_qualifications INNER JOIN qualification_titles ON qualification_titles.id = staff_qualifications.qualification_title_id INNER JOIN ( SELECT staff_qualifications.staff_id staff_id ,MAX(qualification_titles.order) highest_qualification FROM staff_qualifications INNER JOIN qualification_titles ON qualification_titles.id = staff_qualifications.qualification_title_id GROUP BY staff_qualifications.staff_id ) qualification_level ON qualification_level.staff_id = staff_qualifications.staff_id AND qualification_level.highest_qualification = qualification_titles.order GROUP BY staff_qualifications.staff_id ) AS qualification_staff ON qualification_staff.staff_id = staff_data.staff_id INNER JOIN institution_subjects ON institution_subjects.id = institution_subject_staff.institution_subject_id INNER JOIN security_users ON security_users.id = institution_subject_staff.staff_id INNER JOIN genders ON genders.id = security_users.gender_id INNER JOIN education_grades ON education_grades.id = institution_subjects.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = institution_subjects.academic_period_id GROUP BY academic_periods.id ,institution_sectors.id ,education_level_isced.id ,education_programmes.id ,genders.id ,qualification_staff.titles_ID ,security_users.id ) subq GROUP BY subq.academic_period_id ,subq.institution_sector_id ,subq.education_level_isced_id ,subq.education_programme_id ,subq.titles_ID ,subq.staff_gender_id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_programme_sector_specialization_genders`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `institution_sector_id` int(11) NOT NULL, `institution_sector_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_system_id` int(11) NOT NULL, `education_system_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_id` int(11) NOT NULL, `education_level_isced_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_level` int(11) NOT NULL, `education_level_id` int(11) NOT NULL, `education_level_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_cycle_id` int(11) NOT NULL, `education_cycle_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_id` int(11) NOT NULL, `education_programme_code` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_programme_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `staff_gender_id` int(11) NOT NULL, `staff_gender_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `staff_training_category_id` int(11) NOT NULL, `staff_training_category_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `total_staff_teaching` int(11) NOT NULL, `total_staff_teaching_newly_recruited` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_programme_sector_specialization_genders_truncate", "TRUNCATE summary_programme_sector_specialization_genders;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_programme_sector_specialization_genders_insert", "INSERT INTO summary_programme_sector_specialization_genders (academic_period_id, academic_period_name, institution_sector_id, institution_sector_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, education_level_id, education_level_name, education_cycle_id, education_cycle_name, education_programme_id, education_programme_code, education_programme_name, staff_gender_id, staff_gender_name, staff_training_category_id, staff_training_category_name, total_staff_teaching, total_staff_teaching_newly_recruited) SELECT subq.academic_period_id ,subq.academic_period_name ,subq.institution_sector_id ,subq.institution_sector_name ,subq.education_system_id ,subq.education_system_name ,subq.education_level_isced_id ,subq.education_level_isced_name ,subq.education_level_isced_level ,subq.education_level_id ,subq.education_level_name ,subq.education_cycle_id ,subq.education_cycle_name ,subq.education_programme_id ,subq.education_programme_code ,subq.education_programme_name ,subq.staff_gender_id ,subq.staff_gender_name ,subq.specialization_ID staff_training_category_id ,subq.specialization_Name staff_training_category_name ,COUNT(DISTINCT(subq.staff_id)) staff_teaching_count_total ,SUM(CASE WHEN subq.staff_start_date BETWEEN DATE_SUB(subq.academic_period_start_date,INTERVAL 12 MONTH) AND subq.academic_period_end_date THEN 1 ELSE 0 END) staff_teaching_newly_recruited_count FROM ( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,education_levels.id education_level_id ,education_levels.name education_level_name ,education_cycles.id education_cycle_id ,education_cycles.name education_cycle_name ,education_programmes.id education_programme_id ,education_programmes.code education_programme_code ,education_programmes.name education_programme_name ,genders.id staff_gender_id ,genders.name staff_gender_name ,specialization_staff.specialization_ID ,specialization_staff.specialization_Name ,institution_subject_staff.staff_id staff_id ,staff_data.staff_start_date staff_start_date ,academic_periods.start_date academic_period_start_date ,academic_periods.end_date academic_period_end_date FROM institution_subject_staff INNER JOIN institutions ON institutions.id = institution_subject_staff.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN ( SELECT institution_staff.staff_id ,institution_staff.institution_id ,MIN(institution_staff.start_date) staff_start_date FROM institution_staff INNER JOIN institution_positions ON institution_positions.id = institution_staff.institution_position_id AND institution_positions.institution_id = institution_staff.institution_id INNER JOIN staff_position_titles ON staff_position_titles.id = institution_positions.staff_position_title_id WHERE staff_position_titles.type = 1 GROUP BY institution_staff.staff_id ,institution_staff.institution_id) staff_data ON staff_data.staff_id = institution_subject_staff.staff_id AND staff_data.institution_id = institution_subject_staff.institution_id INNER JOIN ( SELECT staff_trainings.staff_id staff_id ,staff_training_categories.id specialization_ID ,staff_training_categories.name specialization_Name ,specialization_level.highest_specialization highest_specialization FROM staff_trainings INNER JOIN staff_training_categories ON staff_training_categories.id = staff_trainings.staff_training_category_id INNER JOIN ( SELECT staff_trainings.staff_id staff_id ,MAX(staff_training_categories.order) highest_specialization FROM staff_trainings INNER JOIN staff_training_categories ON staff_training_categories.id = staff_trainings.staff_training_category_id GROUP BY staff_trainings.staff_id ) specialization_level ON specialization_level.staff_id = staff_trainings.staff_id AND specialization_level.highest_specialization = staff_training_categories.order GROUP BY staff_trainings.staff_id ) AS specialization_staff ON specialization_staff.staff_id = staff_data.staff_id INNER JOIN institution_subjects ON institution_subjects.id = institution_subject_staff.institution_subject_id INNER JOIN security_users ON security_users.id = institution_subject_staff.staff_id INNER JOIN genders ON genders.id = security_users.gender_id INNER JOIN education_grades ON education_grades.id = institution_subjects.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = institution_subjects.academic_period_id GROUP BY academic_periods.id ,institution_sectors.id ,education_level_isced.id ,education_programmes.id ,genders.id ,specialization_staff.specialization_ID ,security_users.id ) subq GROUP BY subq.academic_period_id ,subq.institution_sector_id ,subq.education_level_isced_id ,subq.education_programme_id ,subq.specialization_ID ,subq.staff_gender_id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_isced_sectors`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `institution_sector_id` int(11) NOT NULL, `institution_sector_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_system_id` int(11) NOT NULL, `education_system_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_id` int(11) NOT NULL, `education_level_isced_name` varchar(50) COLLATE utf8_general_ci NOT NULL, `education_level_isced_level` int(11) NOT NULL, `total_instiutions` int(7) NOT NULL, `total_electricity_institutions` int(7) NOT NULL, `total_computer_institutions` int(7) NOT NULL, `total_teaching_computer_institutions` int(7) NOT NULL, `total_internet_institutions` int(7) NOT NULL, `total_toilet_institutions` int(7) NOT NULL, `total_improved_toilet_institutions` int(7) NOT NULL, `total_single_sex_toilet_institutions` int(7) NOT NULL, `total_improved_single_sex_toilet_institutions` int(7) NOT NULL, `total_in_use_toilet_institutions` int(7) NOT NULL, `total_in_use_improved_toilet_institutions` int(7) NOT NULL, `total_in_use_single_sex_toilet_institutions` int(7) NOT NULL, `total_improved_in_use_single_sex_toilet_institutions` int(7) NOT NULL, `total_drinking_water_institutions` int(7) NOT NULL, `total_functional_drinking_water_institutions` int(7) NOT NULL, `total_handwashing_facility_institutions` int(7) NOT NULL, `total_accessible_room_institutions` int(7) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_isced_sectors_truncate", "TRUNCATE summary_isced_sectors;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_isced_sectors_insert", "INSERT INTO summary_isced_sectors (academic_period_id, academic_period_name, institution_sector_id, institution_sector_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, total_instiutions, total_electricity_institutions, total_computer_institutions, total_teaching_computer_institutions, total_internet_institutions, total_toilet_institutions, total_improved_toilet_institutions, total_single_sex_toilet_institutions, total_improved_single_sex_toilet_institutions, total_in_use_toilet_institutions, total_in_use_improved_toilet_institutions, total_in_use_single_sex_toilet_institutions, total_improved_in_use_single_sex_toilet_institutions, total_drinking_water_institutions, total_functional_drinking_water_institutions, total_handwashing_facility_institutions, total_accessible_room_institutions) SELECT main_query.academic_period_id ,main_query.academic_period_name ,main_query.institution_sector_id ,main_query.institution_sector_name ,main_query.education_system_id ,main_query.education_system_name ,main_query.education_level_isced_id ,main_query.education_level_isced_name ,main_query.education_level_isced_level ,COUNT(DISTINCT(main_query.institution_id)) count_total_instiutions ,IFNULL(COUNT(DISTINCT(electricity_info.institution_id)), 0) count_electricity_institutions ,IFNULL(COUNT(DISTINCT(computer_info.institution_id)), 0) count_computer_institutions ,IFNULL(COUNT(DISTINCT(teaching_computer_info.institution_id)), 0) count_teaching_computer_institutions ,IFNULL(COUNT(DISTINCT(internet_info.institution_id)), 0) count_internet_institutions ,IFNULL(COUNT(DISTINCT(toilet_info.institution_id)), 0) count_toilet_institutions ,IFNULL(COUNT(DISTINCT(improved_toilet_info.institution_id)), 0) count_improved_toilet_institutions ,IFNULL(COUNT(DISTINCT(single_sex_toilet_info.institution_id)), 0) count_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(single_sex_improved_toilet_info.institution_id)), 0) count_improved_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(in_use_toilet_info.institution_id)), 0) count_in_use_toilet_institutions ,IFNULL(COUNT(DISTINCT(in_use_improved_toilet_info.institution_id)), 0) count_in_use_improved_toilet_institutions ,IFNULL(COUNT(DISTINCT(in_use_single_sex_toilet_info.institution_id)), 0) count_in_use_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(improved_in_use_single_sex_toilet_info.institution_id)), 0) count_improved_in_use_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(drinking_water_info.institution_id)), 0) count_drinking_water_institutions ,IFNULL(COUNT(DISTINCT(available_drinking_water_info.institution_id)), 0) count_functional_drinking_water_institutions ,IFNULL(COUNT(DISTINCT(handwashing_facility_info.institution_id)), 0) count_handwashing_facility_institutions ,IFNULL(COUNT(DISTINCT(accessible_rooms_info.institution_id)), 0) count_accessible_room_institutions FROM( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,institutions.id institution_id FROM institution_grades INNER JOIN institutions ON institutions.id = institution_grades.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN education_grades ON education_grades.id = institution_grades.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = education_systems.academic_period_id INNER JOIN( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,MAX(education_level_isced.isced_level) education_level_isced_level ,institutions.id institution_id FROM institution_grades INNER JOIN institutions ON institutions.id = institution_grades.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN education_grades ON education_grades.id = institution_grades.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = education_systems.academic_period_id WHERE institutions.institution_status_id = 1 GROUP BY academic_periods.id ,institution_sectors.id ,institutions.id) max_isced_level ON max_isced_level.academic_period_id = academic_periods.id AND max_isced_level.institution_sector_id = institution_sectors.id AND max_isced_level.institution_id = institutions.id AND max_isced_level.education_level_isced_level = education_level_isced.isced_level WHERE institutions.institution_status_id = 1 GROUP BY academic_periods.id ,institution_sectors.id ,education_level_isced.id ,institutions.id) main_query LEFT JOIN ( SELECT infrastructure_utility_electricities.academic_period_id ,infrastructure_utility_electricities.institution_id FROM infrastructure_utility_electricities GROUP BY infrastructure_utility_electricities.academic_period_id ,infrastructure_utility_electricities.institution_id ) electricity_info ON electricity_info.academic_period_id = main_query.academic_period_id AND electricity_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT institution_assets.academic_period_id ,institution_assets.institution_id FROM institution_assets INNER JOIN asset_types ON asset_types.id = institution_assets.asset_type_id WHERE institution_assets.asset_status_id = 1 AND asset_types.name LIKE `%Computer%` OR asset_types.name LIKE `%Laptop%` GROUP BY institution_assets.academic_period_id ,institution_assets.institution_id ) computer_info ON computer_info.academic_period_id = main_query.academic_period_id AND computer_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT institution_assets.academic_period_id ,institution_assets.institution_id FROM institution_assets INNER JOIN asset_types ON asset_types.id = institution_assets.asset_type_id WHERE institution_assets.purpose = 1 AND institution_assets.asset_status_id = 1 AND asset_types.name LIKE `%Computer%` OR asset_types.name LIKE `%Laptop%` GROUP BY institution_assets.academic_period_id ,institution_assets.institution_id ) teaching_computer_info ON teaching_computer_info.academic_period_id = main_query.academic_period_id AND teaching_computer_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_utility_internets.academic_period_id ,infrastructure_utility_internets.institution_id FROM infrastructure_utility_internets GROUP BY infrastructure_utility_internets.academic_period_id ,infrastructure_utility_internets.institution_id ) internet_info ON internet_info.academic_period_id = main_query.academic_period_id AND internet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) toilet_info ON toilet_info.academic_period_id = main_query.academic_period_id AND toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE `%Improved%` GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) improved_toilet_info ON improved_toilet_info.academic_period_id = main_query.academic_period_id AND improved_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) single_sex_toilet_info ON single_sex_toilet_info.academic_period_id = main_query.academic_period_id AND single_sex_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE `%Improved%` GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) single_sex_improved_toilet_info ON single_sex_improved_toilet_info.academic_period_id = main_query.academic_period_id AND single_sex_improved_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) in_use_toilet_info ON in_use_toilet_info.academic_period_id = main_query.academic_period_id AND in_use_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE `%Improved%` GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) in_use_improved_toilet_info ON in_use_improved_toilet_info.academic_period_id = main_query.academic_period_id AND in_use_improved_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) in_use_single_sex_toilet_info ON in_use_single_sex_toilet_info.academic_period_id = main_query.academic_period_id AND in_use_single_sex_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE `%Improved%` GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) improved_in_use_single_sex_toilet_info ON improved_in_use_single_sex_toilet_info.academic_period_id = main_query.academic_period_id AND improved_in_use_single_sex_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id FROM infrastructure_wash_waters GROUP BY infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id ) drinking_water_info ON drinking_water_info.academic_period_id = main_query.academic_period_id AND drinking_water_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id FROM infrastructure_wash_waters INNER JOIN infrastructure_wash_water_functionalities ON infrastructure_wash_water_functionalities.id = infrastructure_wash_waters.infrastructure_wash_water_functionality_id WHERE infrastructure_wash_water_functionalities.name NOT LIKE `%Not Functional%` GROUP BY infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id ) available_drinking_water_info ON available_drinking_water_info.academic_period_id = main_query.academic_period_id AND available_drinking_water_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_hygienes.academic_period_id ,infrastructure_wash_hygienes.institution_id FROM infrastructure_wash_hygienes INNER JOIN infrastructure_wash_hygiene_types ON infrastructure_wash_hygiene_types.id = infrastructure_wash_hygienes.infrastructure_wash_hygiene_type_id WHERE infrastructure_wash_hygiene_types.name NOT LIKE `%Not Available%` GROUP BY infrastructure_wash_hygienes.academic_period_id ,infrastructure_wash_hygienes.institution_id ) handwashing_facility_info ON handwashing_facility_info.academic_period_id = main_query.academic_period_id AND handwashing_facility_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT institution_rooms.academic_period_id ,institution_rooms.institution_id FROM institution_rooms WHERE institution_rooms.accessibility = 1 GROUP BY institution_rooms.academic_period_id ,institution_rooms.institution_id ) accessible_rooms_info ON accessible_rooms_info.academic_period_id = main_query.academic_period_id AND accessible_rooms_info.institution_id = main_query.institution_id GROUP BY main_query.academic_period_id ,main_query.institution_sector_id ,main_query.education_level_isced_id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        
        $this->execute("INSERT INTO `security_functions` (`id`, `name`, `controller`, `module`, `category`, `parent_id`, `_view`, `_edit`, `_add`, `_delete`, `_execute`, `order`, `visible`, `description`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES (NULL, 'UIS Statistics', 'Reports', 'Reports', 'Reports', '-1', 'UisStatistics.index', NULL, 'UisStatistics.add', 'UisStatistics.delete', 'UisStatistics.download', '330', '1', NULL, NULL, NULL, '2', NOW())");
    }
         
    // rollback
    public function down()
    {
        // Restore table
        $this->execute('DROP TABLE IF EXISTS `report_queries`');
        $this->execute('RENAME TABLE `zz_4570_report_queries` TO `report_queries`');

        $this->execute('DROP TABLE IF EXISTS `security_functions`');
        $this->execute('RENAME TABLE `zz_4570_security_functions` TO `security_functions`');

        // Drop summary tables
        $this->execute('DROP TABLE IF EXISTS `summary_programme_sector_genders`');
        $this->execute('DROP TABLE IF EXISTS `summary_grade_gender_ages`');
        $this->execute('DROP TABLE IF EXISTS `summary_grade_status_genders`');
        $this->execute('DROP TABLE IF EXISTS `summary_programme_sector_qualification_genders`');
        $this->execute('DROP TABLE IF EXISTS `summary_programme_sector_specialization_genders`');
        $this->execute('DROP TABLE IF EXISTS `summary_isced_sectors`');

    }
}